package com.bot.adms.dao.impl;

import com.bot.adms.dao.IAdSpScDetailDao;
import com.bot.adms.model.entity.AdSpScDetail;
import com.bot.adms.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 广告分屏明细表持久层接口实现
 *
 * @author ZHOUTAO
 */
public class AdSpScDetailDaoImpl implements IAdSpScDetailDao {
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;

    /**
     * 添加广告分屏明细
     *
     * @param adSpScDetail 广告分屏明细,包含:类型，分屏号等等
     * @return Integer 添加的条数
     */
    @Override
    public Integer insertAdSpScDetail(AdSpScDetail adSpScDetail) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "INSERT INTO `bo_adms`.`ad_sp_sc_detail` (`type`, `sc_id`, `ad_id`) VALUES ("+adSpScDetail.getType()+", "+adSpScDetail.getScId()+", "+adSpScDetail.getAdId()+");";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 修改广告分屏明细
     *
     * @param adSpScDetail 广告分屏明细,包含:类型，分屏号等等
     * @return Integer 修改的条数
     */
    @Override
    public Integer updateAdSpScDetail(AdSpScDetail adSpScDetail) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "UPDATE `bo_adms`.`ad_sp_sc_detail` SET `type` = "+adSpScDetail.getType()+", `sc_id` = "+adSpScDetail.getScId()+" WHERE `id` = "+adSpScDetail.getId()+";";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 删除广告分屏明细
     *
     * @param id 广告分屏明细id
     * @return Integer 删除的条数
     */
    @Override
    public Integer deleteAdSpScDetail(Long id) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "DELETE FROM `bo_adms`.`ad_sp_sc_detail` WHERE `id` = "+id+";";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 查找广告分屏明细
     *
     * @param id 广告分屏明细id
     * @return AdSpScDetail 广告分屏明细,包含:类型，分屏号等等
     */
    @Override
    public AdSpScDetail getAdSpScDetailById(Long id) {
        AdSpScDetail adSpScDetail = null;
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "SELECT * FROM `bo_adms`.`ad_sp_sc_detail` WHERE `id` = "+id+";";
            //4.执行sql
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                adSpScDetail = new AdSpScDetail();
                adSpScDetail.setId(rs.getLong("id"));
                adSpScDetail.setType(rs.getInt("type"));
                adSpScDetail.setScId(rs.getInt("sc_id"));
                adSpScDetail.setAdId(rs.getLong("ad_id"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils.close(conn, stmt,rs);
        }
        return adSpScDetail;
    }

    /**
     * 查询广告分屏明细
     *
     * @param adSpScDetail 广告分屏明细,包含:类型，分屏号等等
     * @return List<AdSpScDetail> 广告分屏明细集合,包含:类型，分屏号等等
     */
    @Override
    public List<AdSpScDetail> searchAdSpScDetail(AdSpScDetail adSpScDetail) {
        List<AdSpScDetail> adSpScDetailList = new ArrayList<>();
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "SELECT * FROM `bo_adms`.`ad_sp_sc_detail` WHERE 1=1";
            //根据广告id查询
            if(adSpScDetail.getAdId() != null) {
                sql = sql + " AND ad_id =" + adSpScDetail.getAdId();
            }
            if(adSpScDetail.getType() != null) {
                sql = sql + " AND type =" + adSpScDetail.getType();
            }
            if(adSpScDetail.getScId() != null) {
                sql = sql + " AND sc_id =" + adSpScDetail.getScId();
            }
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                AdSpScDetail adSpScDetail1 = new AdSpScDetail();
                adSpScDetail1.setId(rs.getLong("id"));
                adSpScDetail1.setType(rs.getInt("type"));
                adSpScDetail1.setScId(rs.getInt("sc_id"));
                adSpScDetail1.setAdId(rs.getLong("ad_id"));
                adSpScDetailList.add(adSpScDetail1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils.close(conn, stmt,rs);
        }
        return adSpScDetailList;
    }
}
